package com.coai.warehouse.repository

import com.coai.warehouse.model.*
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import org.springframework.stereotype.Repository

@Repository
interface WarehouseRepository : JpaRepository<Warehouse, String> {
    fun findByCode(code: String): Warehouse?
    fun findByEnabledTrue(): List<Warehouse>
}

@Repository
interface WarehouseLocationRepository : JpaRepository<WarehouseLocation, String> {
    fun findByWarehouseId(warehouseId: String): List<WarehouseLocation>
    fun findByWarehouseIdAndStatus(warehouseId: String, status: LocationStatus): List<WarehouseLocation>
    fun findByCode(code: String): WarehouseLocation?
    
    @Query("""
        SELECT l FROM WarehouseLocation l 
        WHERE l.warehouseId = :warehouseId 
        AND l.status = 'AVAILABLE'
        AND (l.capacity - l.usedCapacity) >= :requiredCapacity
        ORDER BY l.recommendScore DESC NULLS LAST
    """)
    fun findAvailableLocations(warehouseId: String, requiredCapacity: Double, pageable: Pageable): List<WarehouseLocation>
}

@Repository
interface InventoryRepository : JpaRepository<Inventory, String> {
    fun findByWarehouseId(warehouseId: String): List<Inventory>
    fun findByProductId(productId: String): List<Inventory>
    fun findByWarehouseIdAndProductId(warehouseId: String, productId: String): List<Inventory>
    
    @Query("SELECT SUM(i.quantity) FROM Inventory i WHERE i.productId = :productId")
    fun getTotalQuantityByProduct(productId: String): Long?
    
    @Query("""
        SELECT i FROM Inventory i 
        WHERE i.productId = :productId 
        AND i.availableQuantity >= :quantity
        ORDER BY i.expiryDate ASC NULLS LAST, i.inboundTime ASC
    """)
    fun findAvailableInventory(productId: String, quantity: Int, pageable: Pageable): List<Inventory>
}

@Repository
interface InboundOrderRepository : JpaRepository<InboundOrder, String> {
    fun findByOrderNo(orderNo: String): InboundOrder?
    fun findByWarehouseIdOrderByCreatedAtDesc(warehouseId: String, pageable: Pageable): List<InboundOrder>
    fun findByStatus(status: OrderStatus): List<InboundOrder>
}

@Repository
interface InboundItemRepository : JpaRepository<InboundItem, String> {
    fun findByOrderId(orderId: String): List<InboundItem>
}

@Repository
interface OutboundOrderRepository : JpaRepository<OutboundOrder, String> {
    fun findByOrderNo(orderNo: String): OutboundOrder?
    fun findByWarehouseIdOrderByCreatedAtDesc(warehouseId: String, pageable: Pageable): List<OutboundOrder>
    fun findByStatus(status: OrderStatus): List<OutboundOrder>
    
    @Query("""
        SELECT o FROM OutboundOrder o 
        WHERE o.status = :status 
        ORDER BY o.priority DESC, o.plannedTime ASC
    """)
    fun findPendingOrdersByPriority(status: OrderStatus, pageable: Pageable): List<OutboundOrder>
}

@Repository
interface OutboundItemRepository : JpaRepository<OutboundItem, String> {
    fun findByOrderId(orderId: String): List<OutboundItem>
}

@Repository
interface StockCheckOrderRepository : JpaRepository<StockCheckOrder, String> {
    fun findByOrderNo(orderNo: String): StockCheckOrder?
    fun findByWarehouseIdOrderByCreatedAtDesc(warehouseId: String, pageable: Pageable): List<StockCheckOrder>
}
